/****************************************************************************
 *
 * Copyright (C) 2003-2008 Los Alamos National Security, LLC
 *                         Packet Analytics Corporation
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License Version 2 as
 * published by the Free Software Foundation.  You may not use, modify or
 * distribute this program under any other version of the GNU General
 * Public License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 *
 ****************************************************************************/
package nfse.gui.server;

import com.google.gwt.user.server.rpc.*;

import nfse.NetFSE;
import nfse.NetFSERecord;
import nfse.NetFSEUtil;
import nfse.gui.client.QueryService; //import nfse.gui.client.NetFSEClient;
import nfse.gui.query.*;
import nfse.gui.record.*;

import java.net.*;
import java.io.*;
import java.sql.*;
import java.util.*;

public class QueryServiceImpl extends RemoteServiceServlet implements
		QueryService {

	private static Connection connectionResult = null;

	private static Connection connectionMetadata = null;

	private String[] predicates = { "=", "~", "&lt;", "&gt;", "&lt;=", "&gt;=" };

	public QueryServiceImpl() {
		super();

		try {

		} catch (Exception e) {
			e.printStackTrace();
		}

		if (NetFSE.TYPE_CLASS_NAMES == null) {
			try {
				NetFSE.configure(NetFSE.CONFIG_FILE);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private String convertFromHex(String line) {
		byte[] bytes = new byte[line.length() / 2];
		for (int i = 0; i < bytes.length; i++)
			bytes[i] = (byte) Integer.parseInt(
					line.substring(i * 2, i * 2 + 2), 16);
		return new String(bytes);
	}

	private Statement createMetadataStatement() throws Exception {
		Statement s = null;
		try {
			s = connectionMetadata.createStatement();
		} catch (Exception e) {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://" + NetFSE.METADATA_HOST + "/"
					+ NetFSE.METADATA_DATABASE;
			Properties props = new Properties();
			props.setProperty("user", NetFSE.METADATA_USER);
			props.setProperty("password", NetFSE.METADATA_PASSWORD);
			props.setProperty("client_encoding", "UTF8");

			connectionMetadata = DriverManager.getConnection(url, props);

			s = connectionMetadata.createStatement();
		}
		return s;
	}

	public Statement createResultStatement() throws Exception {
		Statement s = null;
		try {
			s = connectionResult.createStatement();
		} catch (Exception e) {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://" + NetFSE.RESULT_DB_HOST + "/"
					+ NetFSE.RESULT_DB_DATABASE;
			Properties props = new Properties();
			props.setProperty("user", NetFSE.RESULT_DB_USER);
			props.setProperty("password", NetFSE.RESULT_DB_PASSWORD);
			props.setProperty("client_encoding", "UTF8");

			connectionResult = DriverManager.getConnection(url, props);

			s = connectionResult.createStatement();
		}
		return s;
	}

	public void delete(String queryID) {
		try {

			if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
				return;// "This feature disabled for the test drive.";
			}

			Statement s1 = createResultStatement();

			ResultSet rs = s1
					.executeQuery("select * from Queries where Query_ID='"
							+ queryID + "'");
			if (!rs.next()) {
				rs.close();
				s1.close();
				// System.out.println("Invalid query ID specified: " + queryID);
				return;
			} else {
				int status = rs.getInt("Status");
				if (status == 2) {
					rs.close();
					s1.close();
					// System.out.println("Cannot delete a query being
					// merged.");
					return;
				}
			}
			rs.close();

			int[] types = NetFSE.getTypes();
			for (int i = 0; i < types.length; i++) {
				try {
					s1.execute("drop table " + queryID + "_" + types[i]);
				} catch (Exception E) {
				}
			}
			try {
				s1.execute("drop table " + queryID);
			} catch (Exception E) {
			}
			try {
				s1.execute("delete from Queries where Query_ID='" + queryID
						+ "'");
			} catch (Exception E) {
			}
			try {
				s1.execute("delete from Query_Types where Query_ID='" + queryID
						+ "'");
			} catch (Exception E) {
			}

			s1.close();
			// c.close();
		} catch (Exception E) {
			E.printStackTrace();
		}
	}

	public int delete(String queryID, int type, String deleteSQL) {
		try {

			if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
				return 0;// "This feature disabled for the test drive.";
			}

			Statement s1 = createResultStatement();

			ResultSet rs = s1
					.executeQuery("select * from Queries where Query_ID='"
							+ queryID + "'");
			rs.next();
			int status = rs.getInt("Status");
			rs.close();

			s1.execute("delete from " + queryID + "_" + type + " where "
					+ deleteSQL);

			rs = s1.executeQuery("select count(*) as a from " + queryID + "_"
					+ type);
			rs.next();
			int count2 = rs.getInt("a");
			rs.close();

			if (status == 1) {
				String sql = "update Query_Types set Count=" + count2
						+ " where Query_ID='" + queryID + "' and Type_ID="
						+ type + "";
				// System.out.println(sql);
				s1.execute(sql);
			}
			s1.close();
			return count2;
		} catch (Exception E) {
			E.printStackTrace();
		}
		return 0;
	}

	public void deleteQueryOptions(String queryID) {
		try {

			if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
				return;// "This feature disabled for the test drive.";
			}

			Statement s1 = createResultStatement();

			try {
				s1
						.execute("update Query_Options set Deleted=1 where Query_ID='"
								+ queryID + "'");
			} catch (Exception E) {
				E.printStackTrace();
			}

			s1.close();

		} catch (Exception E) {
			E.printStackTrace();
		}
	}

	public QueryResult get(String queryID, int n, int offset, int type,
			String where, String orderBy) {
		String sql = "";
		try {

			Statement s1 = createResultStatement();
			Statement s2 = createResultStatement();
			Statement s3 = createResultStatement();

			QueryResult qr = new QueryResult();
			qr.count = -1;
			// qr.records = new Record[0];
			qr.flexRecordGroup = new FlexRecordGroup();

			Vector records = new Vector();
			String table = queryID + "_" + type;

			NetFSERecord rec = null;
			NetFSERecord tempRec = NetFSE.getTypeClass(type);
			ResultSet rs1 = null;

			sql = "select * from " + queryID + "_" + type + " limit 1";
			try {
				rs1 = s1.executeQuery(sql);
				rs1.close();
			} catch (Exception e) {
				if (rs1 != null)
					rs1.close();
				s1.close();
				s2.close();
				s3.close();
				return null;
			}

			sql = "select * from Queries where Query_ID='" + queryID + "'";
			rs1 = s1.executeQuery(sql);
			if (rs1.next()) {

				Timestamp ts = rs1.getTimestamp("TS");

				Timestamp now = new Timestamp(System.currentTimeMillis());
				sql = "select * from Queries where Query_ID='" + queryID
						+ "' and TS is not null";
				ResultSet rs2 = s2.executeQuery(sql);
				if (rs2.next()) {
					rs2.close();
					try {
						sql = "update Queries set TS='" + now
								+ "' where Query_ID='" + queryID + "'";
						s2.execute(sql);
					} catch (Exception E) {
						E.printStackTrace();
					}
				}

				int status = rs1.getInt("Status");
				int count = -1;
				if (where.length() == 0) {
					if (status == 1) {
						rs1.close();
						sql = "select * from Query_Types where Query_ID='"
								+ queryID + "' and Type_ID=" + type;
						rs1 = s1.executeQuery(sql);
						if (rs1.next())
							count = rs1.getInt("Count");
					} else if (status == 0) {
						rs1.close();
						sql = "select count(*) as a from " + table + "";
						try {
							rs1 = s1.executeQuery(sql);
							if (rs1.next())
								count = rs1.getInt("a");
						} catch (Exception e) {
							s1.close();
							s2.close();
							s3.close();
							return qr;
						}
					}
				} else {
					int idx = where.toUpperCase().indexOf("WHERE");
					if (idx >= 0)
						where = where.substring(idx + 6);
					sql = "select count(*) as a from " + queryID + ", " + table
							+ " where Type_ID=" + type + " and " + queryID
							+ ".ID=" + queryID + "_" + type + ".ID ";
					if (where.length() > 0) {
						sql += " and (" + where + ")";
					}
					rs1 = s1.executeQuery(sql);
					rs1.next();
					count = rs1.getInt("a");

				}
				// System.out.println(sql);
				qr.count = count;

				rs1.close();

				if (offset < 0)
					offset = 0;

				int first = offset + 1;
				int last = offset + n;
				if (last > count)
					last = count;

				String record = "";
				String recordHex = "";
				String line = "";

				int sensor = -1;

				sql = "select * from " + queryID + ", " + table
						+ " where Type_ID=" + type + " and " + queryID + ".ID="
						+ queryID + "_" + type + ".ID ";
				if (where.length() > 0) {
					sql += " and (" + where + ") ";
				}
				if (orderBy.length() > 0) {
					sql += " order by " + orderBy;
				}
				sql += " limit " + n + " offset " + offset;

				// System.out.println(sql);

				try {
					rs1 = s1.executeQuery(sql);
				} catch (Exception e) {
					// System.out.println(sql);
					// e.printStackTrace();

					s1.close();
					s2.close();
					s3.close();
					// c.close();
					return null;
				}

				while (rs1.next()) {
					type = rs1.getInt("Type_ID");
					sensor = rs1.getInt("Sensor_ID");

					record = rs1.getString("Record");
					recordHex = null;
					try {
						recordHex = rs1.getString("Record_Hex");
					} catch (Exception e) {
						// e.printStackTrace();
					}

					line = null;
					if (recordHex != null)
						line = convertFromHex(recordHex);
					else
						line = record;

					try {
						rec = RecordGenerator.generate(line, sensor, type);
					} catch (Exception e) {
						// e.printStackTrace();
						line = convertFromHex(record);
						rec = RecordGenerator.generate(line, sensor, type);
						// System.out.println(line);
						// System.out.println(rec);
					}

					if (rec != null)
						records.addElement(rec.getFlexRecord());
				}
				rs1.close();

			} else {
				rs1.close();
			}

			rs1 = s1.executeQuery("select * from Queries where Query_ID='"
					+ queryID + "'");
			QuerySummary qs = new QuerySummary();
			if (rs1.next()) {

				qs.queryID = rs1.getString("Query_ID");
				qs.status = rs1.getInt("Status");
				qs.count = rs1.getInt("Count");
				qs.ts = rs1.getString("TS");
				Timestamp ts = rs1.getTimestamp("TS");
				if (ts != null) {
					ts = new Timestamp(ts.getTime() + 172800000);
					if (!qs.ts.equals("0000-00-00 00:00:00.0"))
						qs.ts = ts.toString();
				} else {
					qs.ts = "Permanent";
				}
				qs.note = rs1.getString("Note");
				// String[] typeStrs = new String[types.length];
				ResultSet rs2 = s2
						.executeQuery("select * from Query_Types where Query_ID='"
								+ qs.queryID + "'");
				while (rs2.next()) {
					int tempType = rs2.getInt("Type_ID");
					int count2 = rs2.getInt("Count");
					if (qs.status == 0) {
						try {
							ResultSet rs3 = s3
									.executeQuery("select count(*) as a from "
											+ qs.queryID + "_" + tempType);
							rs3.next();
							count2 = rs3.getInt("a");
							rs3.close();
						} catch (Exception e) {

						}
					}
					qs.setTypeCount(tempType, count2);
				}

				rs2.close();

			}

			s1.close();
			s2.close();
			s3.close();
			// c.close();

			FlexRecord[] temp = new FlexRecord[records.size()];
			for (int i = 0; i < temp.length; i++)
				temp[i] = (FlexRecord) records.elementAt(i);

			qr.flexRecordGroup = new FlexRecordGroup();
			qr.flexRecordGroup.typeID = type;
			if (tempRec != null) {
				qr.flexRecordGroup.flexRecordFields = tempRec
						.getFlexRecordFields();
				qr.schema = tempRec.getCreateFieldTableSQL(tempRec
						.getTypeName());
				// System.out.println(qr.schema);
			} else {
				qr.flexRecordGroup.flexRecordFields = new FlexRecordField[0];
			}
			qr.flexRecordGroup.records = temp;
			qr.qs = qs;

			return qr;
		} catch (Exception E) {
			E.printStackTrace();
		}
		return null;
	}

	/***************************************************************************
	 * Method to translate error codes from QuerySocket into human-readable
	 * error messages.
	 * 
	 * @param reply
	 *            The error message returned by the QuerySocket connection
	 * @return
	 */
	public String getError(int reply) {
		String temp = "Unknown error";
		switch (reply) {
		case -1:
			temp = "Query parse error";
			break;
		case -2:
			temp = "MetaDatabase connection error";
			break;
		case -3:
			temp = "User authentication error";
			break;
		case -4:
			temp = "Invalid query error";
			break;
		case -5:
			temp = "Empty query error";
			break;

		}
		return temp;
	}

	public QueryInfo getQueryInfo(String queryID) {
		try {
			QueryInfo info = new QueryInfo();
			Statement statement = null;
			try {
				statement = connectionResult.createStatement();
			} catch (Exception e) {
				e.printStackTrace();
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				connectionResult = DriverManager.getConnection("jdbc:mysql://"
						+ NetFSE.RESULT_DB_HOST + "/"
						+ NetFSE.RESULT_DB_DATABASE, NetFSE.RESULT_DB_USER,
						NetFSE.RESULT_DB_PASSWORD);
				statement = connectionResult.createStatement();
			}

			String sql = "select Note from Queries where Query_ID='" + queryID
					+ "'";
			ResultSet rs = statement.executeQuery(sql);
			if (rs.next()) {
				info.note = ServerUtil.convertFromHex(rs.getString("Note"));
				if (info.note == null)
					info.note = "";
			}
			rs.close();

			Vector types = new Vector();
			sql = "select * from Query_Types where Query_ID='" + queryID + "'";
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				types.addElement(new Integer(rs.getInt("type_id")));
			}
			rs.close();

			info.types = new int[types.size()];
			for (int i = 0; i < info.types.length; i++)
				info.types[i] = ((Integer) types.elementAt(i));

			statement.close();
			return info;
			// c.close();
		} catch (Exception E) {
			E.printStackTrace();
		}
		return null;
	}

	public String getUserName() {
		try {
			String userName = this.getThreadLocalRequest().getRemoteUser();
			if (userName == null || userName.length() == 0)
				userName = "";
			return userName;
		} catch (Exception e) {
			return "";
		}

	}

	private boolean isAdmin(String user) {
		try {
			Statement statement = createMetadataStatement();
			boolean admin = false;
			String userName = getUserName();

			ResultSet rs = statement
					.executeQuery("select * from Users where User_Name='"
							+ userName + "'");
			if (rs.next()) {
				if (rs.getInt("Admin") != 0) {
					admin = true;
				}
			}
			rs.close();
			statement.close();
			return admin;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public void makePermanent(String queryID) {
		try {

			if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
				return;// "This feature disabled for the test drive.";
			}

			Statement s1 = createResultStatement();

			// System.out.println("Make permanent: " + queryID);

			String sql = "update Queries set TS = null where Query_ID='"
					+ queryID + "'";
			// System.out.println(sql);
			s1.execute(sql);

			s1.close();
			// c.close();
		} catch (Exception E) {
			E.printStackTrace();
		}
	}

	public synchronized String merge(String[] queryIDs) {
		String message = "";
		try {

			if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
				return "This feature disabled for the test drive.";
			}

			Statement s1 = createResultStatement();
			Statement s2 = createResultStatement();

			int[] types = NetFSE.getTypes();

			String sql = "select * from Queries where Query_ID = ";
			for (int i = 0; i < queryIDs.length; i++) {
				if (i != 0)
					sql += " or ";
				sql += "'" + queryIDs[i] + "' ";
			}
			ResultSet rs = s1.executeQuery(sql);

			while (rs.next()) {
				String queryID = rs.getString("Query_ID");
				int status = rs.getInt("Status");
				if (status != 1) {
					rs.close();
					s1.close();
					return "Only completed queries can be merged. Please deselect query ID "
							+ queryID + " and try again.";
				}
			}
			rs.close();

			// Set the status of the queries to 2, or "Merging" so that
			// they cannot be deleted and the action is reflected in the GUI
			for (int i = 0; i < queryIDs.length; i++) {
				sql = "update Queries set Status=2 where Query_ID='"
						+ queryIDs[i] + "'";
				s1.execute(sql);
			}

			// Since we don't know what types exist in each query,
			// we must make sure that tables exist for each type.

			int count = 0;

			sql = "select count(*) as a from " + queryIDs[0];
			rs = s1.executeQuery(sql);
			rs.next();
			count = rs.getInt("a");
			// count = count2;
			rs.close();

			// First we determine which types we need to build
			boolean[] tablesBuilt = new boolean[types.length];
			for (int i = 0; i < queryIDs.length; i++) {

				sql = "select * from Query_Types where Query_ID='"
						+ queryIDs[i] + "'";
				rs = s1.executeQuery(sql);
				while (rs.next()) {
					int typeID = rs.getInt("Type_ID");
					for (int x = 0; x < tablesBuilt.length; x++) {
						if (types[x] == typeID) {
							tablesBuilt[x] = true;
							break;
						}
					}
				}
				rs.close();
			}

			// Now that we know what to build, try to build the tables and
			// indicies
			for (int i = 0; i < types.length; i++) {
				if (tablesBuilt[i] == true) {
					try {
						NetFSERecord rec = NetFSE.getTypeClass(types[i]);
						String fieldTableSQL = rec
								.getCreateFieldTableSQL(queryIDs[0] + "_"
										+ types[i]);
						String[] indexSQL = rec
								.getIndexFieldTableSQL(queryIDs[0] + "_"
										+ types[i]);

						s1.execute(fieldTableSQL);
						for (int x = 0; x < indexSQL.length; x++) {
							try {
								s1.execute(indexSQL[x]);
							} catch (Exception e) {
								// no op
							}
						}
					} catch (Exception e) {
						// e.printStackTrace();
					}
				}
			}

			// Now that everything is set up, perform the actual merge by
			// selecting from the secondary queries and inserting into the
			// primary (first) query
			final int itSize = 100000;
			for (int i = 1; i < queryIDs.length; i++) {
				sql = "select count(*) as a from " + queryIDs[i] + " ";
				rs = s1.executeQuery(sql);
				rs.next();
				int count2 = rs.getInt("a");
				rs.close();

				int iterations = count2 / itSize + 1;
				for (int j = 0; j < iterations; j++) {
					sql = "select * from " + queryIDs[i] + " limit " + itSize
							+ " offset " + j * itSize;
					// System.out.println(sql);
					rs = s1.executeQuery(sql);
					while (rs.next()) {
						count++;
						int typeID = rs.getInt("Type_ID");
						int sensorID = rs.getInt("Sensor_ID");
						String line = NetFSEUtil.convertFromHex(rs
								.getString("Record"));
						// System.out.println(line);
						NetFSERecord rec = NetFSE.getTypeClass(typeID);
						rec = rec.generate(line, sensorID);
						String insertSQL = " insert into " + queryIDs[0]
								+ " values (" + count + "," + typeID + ","
								+ sensorID + ",'"
								+ NetFSEUtil.convertToHex(line) + "')";
						s2.execute(insertSQL);
						insertSQL = rec.getInsertFieldTableSQL(count,
								queryIDs[0] + "_" + typeID);
						s2.execute(insertSQL);
					}
					rs.close();
				}
			}

			s2.close();

			// Now that we are done, set the query status of the primary query
			// ID to 1 and delete the rest
			sql = "update Queries set Status=1 where Query_ID='" + queryIDs[0]
					+ "'";
			s1.execute(sql);

			for (int i = 1; i < queryIDs.length; i++) {
				sql = "update Queries set Status=1 where Query_ID='"
						+ queryIDs[i] + "'";
				s1.execute(sql);
				delete(queryIDs[i]);
			}

			// Lastly, compute the counts for each type and update/insert the
			// stat entries
			int totalRecs = 0;
			for (int i = 0; i < types.length; i++) {
				try {
					sql = "select count(*) as a from " + queryIDs[0] + "_"
							+ types[i] + " ";
					rs = s1.executeQuery(sql);
					rs.next();
					int count2 = rs.getInt("a");
					totalRecs += count2;
					rs.close();

					try {
						sql = "insert into Query_Types values ('" + queryIDs[0]
								+ "'," + types[i] + ",0," + count2 + ")";
						s1.execute(sql);
					} catch (Exception e) {
						sql = "update Query_Types set Count=" + count2
								+ " where Query_ID='" + queryIDs[0]
								+ "' and Type_ID=" + types[i];
						s1.execute(sql);
					}
				} catch (Exception e2) {
					// e2.printStackTrace();
				}
			}
			sql = "update Queries set Count=" + totalRecs + " where Query_ID='"
					+ queryIDs[0] + "' ";
			s1.execute(sql);

			s1.close();

			message = "Merge complete.";

		} catch (Exception E) {
			E.printStackTrace();
			message = "An error occurred while merging the queries: '"
					+ E.getMessage() + "'.";
		}
		return message;
	}

	public void multiDelete(String[] queryIDs) {

		for (int i = 0; i < queryIDs.length; i++) {
			delete(queryIDs[i]);
		}
	}

	public void multiMakePermanent(String[] queryIDs) {
		for (int i = 0; i < queryIDs.length; i++) {
			makePermanent(queryIDs[i]);
		}
	}

	public void multiQueryHistoryDelete(String[] queryIDs) {
		for (int i = 0; i < queryIDs.length; i++) {
			deleteQueryOptions(queryIDs[i]);
		}
	}

	public QuerySummary[] myQueries() {
		QuerySummary[] results = null;
		try {

			Statement s1 = createResultStatement();
			Statement s2 = createResultStatement();
			Statement s3 = createResultStatement();

			Vector temp = new Vector();
			String userName = getUserName();

			String sql = "select * from Queries where (User_ID='" + userName
					+ "' or User_ID='') order by TS desc";
			// System.out.println(sql);
			ResultSet rs1 = s1.executeQuery(sql);

			// ResultSet rs1 = s1.executeQuery("select * from Queries ");
			while (rs1.next()) {
				QuerySummary qs = new QuerySummary();
				qs.queryID = rs1.getString("Query_ID");
				qs.status = rs1.getInt("Status");
				qs.count = rs1.getInt("Count");
				qs.ts = rs1.getString("TS");
				Timestamp ts = rs1.getTimestamp("TS");
				if (qs.ts != null && qs.ts.equals("0000-00-00 00:00:00.0")) {
					qs.ts = "Never";
				} else if (ts != null) {
					ts = new Timestamp(ts.getTime() + ServerUtil.EXPIRE_HOURS
							* 60 * 60 * 1000);
					if (!qs.ts.equals("0000-00-00 00:00:00.0"))
						qs.ts = ts.toString();
				} else {
					qs.ts = "Never";
				}
				qs.note = ServerUtil.convertFromHex(rs1.getString("Note"));
				ResultSet rs2 = s2
						.executeQuery("select * from Query_Types where Query_ID='"
								+ qs.queryID + "'");
				while (rs2.next()) {
					int type = rs2.getInt("Type_ID");
					int count2 = rs2.getInt("Count");
					if (qs.status == 0) {
						try {
							ResultSet rs3 = s3
									.executeQuery("select count(*)  as a from "
											+ qs.queryID + "_" + type);
							rs3.next();
							count2 = rs3.getInt("a");
							rs3.close();
						} catch (Exception e) {
							// e.printStackTrace();
						}
					}
					qs.setTypeCount(type, count2);
				}

				rs2.close();
				temp.addElement(qs);
			}

			rs1.close();
			s1.close();
			s2.close();
			s3.close();
			// c.close();

			results = new QuerySummary[temp.size()];
			for (int i = 0; i < results.length; i++) {
				results[i] = (QuerySummary) temp.elementAt(i);
			}

		} catch (Exception E) {
			this.getServletContext().log(E.getMessage(), E);
			E.printStackTrace();
		}
		return results;
	}

	public String query(String query, String note, int[] types, int limit,
			int total) {
		Socket socket = null;
		Socket socket2 = null;
		DataInputStream resultIn = null;
		try {
			Statement s1 = createResultStatement();

			String userName = getUserName();

			if (note == null)
				note = "";
			if (note.length() > 0) {
				try {
					NetFSEUtil.convertFromHex(note);

				} catch (Exception e) {
					note = NetFSEUtil.convertToHex(note);
				}
			}

			Vector tempQueries = new Vector();
			String queryHead = query.substring(0, query.indexOf(">") + 1);
			String queryBody = query.substring(query.indexOf(">") + 1);
			for (int i = 0; i < types.length; i++) {
				try {
					// tempTypes2.addElement(new Integer(tempTypes[i].trim()));

					tempQueries.addElement(queryHead + "<Data type=\""
							+ types[i] + "\"/>" + queryBody);
					// out.println(query);
				} catch (Exception E) {
					// no op
				}
			}
			// query += queryBody;

			String[] queries = new String[tempQueries.size()];
			for (int i = 0; i < queries.length; i++)
				queries[i] = (String) tempQueries.elementAt(i);

			Vector tempRecords = new Vector();
			int maxTypeID = 0;

			ResultSet rs = null;

			// int[] counts = new int[1000];

			boolean[] tableBuilt = new boolean[types.length];

			String queryID = "";

			ResultSet rs1 = s1.executeQuery("select nextval('query_id')");
			rs1.next();
			queryID = "s" + rs1.getInt(1);
			rs1.close();

			String resultTableSQL = "create table " + queryID
					+ " (ID bigint, Type_ID int, Sensor_ID int, Record text)";
			try {

				s1.execute(resultTableSQL);
				// worked = true;
				String indexSQL = "create index " + queryID + "_id_idx on "
						+ queryID + " (ID)";
				s1.execute(indexSQL);
				indexSQL = "create index " + queryID + "_type_idx on "
						+ queryID + " (Type_ID)";
				s1.execute(indexSQL);
				indexSQL = "create index " + queryID + "_sensor_idx on "
						+ queryID + " (Sensor_ID)";
				s1.execute(indexSQL);
			} catch (Exception E) {
				// System.out.println(resultTableSQL);
				// E.printStackTrace();
				// qid++;
				Thread.sleep(1000);
			}

			String[] indexSQL = null;
			String fieldTableSQL = "";
			String[] appendIndexSQL = null;
			String appendFieldTableSQL = "";

			for (int i = 0; i < types.length; i++) {
				// NetForSERecord rec = records[types[i]];
				NetFSERecord rec = NetFSE.getTypeClass(types[i]);
				fieldTableSQL = rec.getCreateFieldTableSQL(queryID + "_"
						+ types[i]);
				indexSQL = rec.getIndexFieldTableSQL(queryID + "_" + types[i]);

				s1.execute(fieldTableSQL);
				for (int x = 0; x < indexSQL.length; x++) {
					try {
						s1.execute(indexSQL[x]);
					} catch (Exception e) {
						// System.out.println(indexSQL[x]);
						e.printStackTrace();
					}
				}

			}

			String insertSQL = "";

			try {
				insertSQL = "insert into Queries values ('" + queryID
						+ "',0,0,'" + userName + "','"
						+ new Timestamp(System.currentTimeMillis()) + "','"
						+ note + "')";
				s1.execute(insertSQL);
				for (int i = 0; i < types.length; i++) {
					insertSQL = "insert into Query_Types values ('" + queryID
							+ "'," + types[i] + ",0)";
					s1.execute(insertSQL);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			QueryThread qt = new QueryThread(queryID, queries, types, s1,
					total, limit);
			qt.start();
			return queryID;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "failed";

	}

	public QueryOptions[] queryHistory() {
		QueryOptions[] results = null;
		try {

			Statement s1 = createResultStatement();

			Vector temp = new Vector();
			String userName = getUserName();

			// Statement s2 = c.createStatement();
			ResultSet rs1 = s1
					.executeQuery("select * from Query_Options where (User_ID='"
							+ userName
							+ "' or User_ID='') and Deleted=0 order by TS desc");
			// ResultSet rs1 = s1.executeQuery("select * from Queries ");
			while (rs1.next()) {

				QueryOptions qo = new QueryOptions();
				qo.queryID = rs1.getString("Query_ID");
				qo.queryType = rs1.getInt("Query_Type");
				qo.ts = rs1.getString("TS");
				qo.startTS = rs1.getString("Start_TS");
				String endTS = rs1.getString("End_TS");
				if (endTS != null && !endTS.equals("null")) {
					qo.endTS = endTS;
				} else
					qo.endTS = "";
				qo.srcIP1 = rs1.getString("Src_IP_1");
				qo.srcIP2 = rs1.getString("Src_IP_2");
				qo.dstIP1 = rs1.getString("Dst_IP_1");
				qo.dstIP2 = rs1.getString("Dst_IP_2");
				qo.dstPort1 = rs1.getInt("Dst_Port_1");
				qo.dstPort2 = rs1.getInt("Dst_Port_2");
				qo.dstIPPred = rs1.getInt("Dst_IP_Pred");
				qo.srcIPPred = rs1.getInt("Src_IP_Pred");
				qo.dstPortPred = rs1.getInt("Dst_Port_Pred");
				qo.ips = rs1.getString("IPs");
				qo.ipSearchType = rs1.getInt("IP_Search_Type");
				// qo.note = ServerUtil.convertFromHex(rs1.getString("Note"));
				qo.note = "";
				qo.limit = rs1.getInt("Type_Limit");
				String[] typeStrs = rs1.getString("Types").split(";");
				// int[] types = new int[typeStrs.length];
				for (int i = 0; i < typeStrs.length; i++) {
					if (typeStrs[i].length() > 0)
						qo.setType(Integer.parseInt(typeStrs[i]), true);
				}
				temp.addElement(qo);
			}

			rs1.close();
			s1.close();
			// s2.close();
			// c.close();

			results = new QueryOptions[temp.size()];
			for (int i = 0; i < results.length; i++) {
				results[i] = (QueryOptions) temp.elementAt(i);
			}
		} catch (Exception E) {
			E.printStackTrace();
		}
		return results;
	}

	public QueryOptions getSearchQuery(String queryId) {
		QueryOptions qo = null;
		new QueryOptions();
		try {

			Statement s1 = createResultStatement();

			ResultSet rs1 = s1
					.executeQuery("select * from Query_Options where query_id =  \'"
							+ queryId + "\'");
			if (rs1.next()) {
				qo = new QueryOptions();
				qo.queryID = rs1.getString("Query_ID");
				qo.queryType = rs1.getInt("Query_Type");
				qo.ts = rs1.getString("TS");
				qo.startTS = rs1.getString("Start_TS");
				String endTS = rs1.getString("End_TS");
				if (endTS != null && !endTS.equals("null")) {
					qo.endTS = endTS;
				} else
					qo.endTS = "";
				qo.srcIP1 = rs1.getString("Src_IP_1");
				qo.srcIP2 = rs1.getString("Src_IP_2");
				qo.dstIP1 = rs1.getString("Dst_IP_1");
				qo.dstIP2 = rs1.getString("Dst_IP_2");
				qo.dstPort1 = rs1.getInt("Dst_Port_1");
				qo.dstPort2 = rs1.getInt("Dst_Port_2");
				qo.dstIPPred = rs1.getInt("Dst_IP_Pred");
				qo.srcIPPred = rs1.getInt("Src_IP_Pred");
				qo.dstPortPred = rs1.getInt("Dst_Port_Pred");
				qo.ips = rs1.getString("IPs");
				qo.ipSearchType = rs1.getInt("IP_Search_Type");
				// qo.note = ServerUtil.convertFromHex(rs1.getString("Note"));
				qo.note = "";
				qo.limit = rs1.getInt("Type_Limit");
				String[] typeStrs = rs1.getString("Types").split(";");
				for (int i = 0; i < typeStrs.length; i++) {
					// System.out.println("TYPE: " + typeStrs[i]);
					if (typeStrs[i].length() > 0)
						qo.setType(Integer.parseInt(typeStrs[i]), true);
				}
			}

			rs1.close();
			s1.close();

		} catch (Exception E) {
			E.printStackTrace();
		}
		return qo;
	}

	public void saveQueryOptions(QueryOptions options) {
		try {

			Statement s1 = createResultStatement();

			String userName = getUserName();

			if (options.queryID == null)
				options.queryID = userName;

			// System.out.println("Save Query Options: " + options.queryID);

			String sql = "insert into Query_Options values (";
			sql += "'" + options.queryID + "'";
			sql += ",";
			sql += "" + options.queryType + "";
			sql += ",";
			sql += "'" + options.startTS + "'";
			sql += ",";
			sql += "'" + options.endTS + "'";
			sql += ",";
			sql += "'" + options.srcIP1 + "'";
			sql += ",";
			sql += "'" + options.srcIP2 + "'";
			sql += ",";
			sql += "" + options.srcIPPred + "";
			sql += ",";
			sql += "'" + options.dstIP1 + "'";
			sql += ",";
			sql += "'" + options.dstIP2 + "'";
			sql += ",";
			sql += "" + options.dstIPPred + "";
			sql += ",";
			sql += "" + options.dstPort1 + "";
			sql += ",";
			sql += "" + options.dstPort2 + "";
			sql += ",";
			sql += "" + options.dstPortPred + "";
			sql += ",";
			sql += "''";
			sql += ",";
			sql += "''";
			sql += ",";
			sql += "0";
			sql += ",";
			sql += "'" + options.ips + "'";
			sql += ",";
			sql += "" + options.ipSearchType + "";
			sql += ",";
			sql += "'" + options.note + "'";
			sql += ",";
			sql += "'" + userName + "'";
			sql += ",";
			sql += "'" + (new Timestamp(System.currentTimeMillis())).toString()
					+ "'";
			sql += ",";
			int[] types = options.getTypes();
			String typeStr = "";
			for (int i = 0; i < types.length; i++) {
				if (i > 0)
					typeStr += ";";
				typeStr += "" + types[i];
			}
			sql += "'" + typeStr + "'";
			sql += ",";
			sql += "" + options.limit + "";
			sql += ",0";
			sql += ")";

			System.out.println(sql);
			s1.execute(sql);

			s1.close();
			// c.close();
		} catch (Exception E) {
			E.printStackTrace();
		}
	}

	public void setNote(String queryID, String note) {
		try {

			Statement s1 = createResultStatement();

			if (note == null)
				note = "";
			if (note.length() > 0) {
				try {
					NetFSEUtil.convertFromHex(note);
				} catch (Exception e) {
					note = NetFSEUtil.convertToHex(note);
				}
			}

			String sql = "update Queries set Note='" + note
					+ "' where Query_ID='" + queryID + "'";
			s1.execute(sql);

			s1.close();
			// c.close();
		} catch (Exception E) {
			E.printStackTrace();
		}
	}

	public QueryResult timeline(String queryID, int n, int[] types,
			String[] where) {
		String sql = "";
		try {
			QueryResult qr = new QueryResult();

			QueryResult[] qrs = new QueryResult[types.length];
			int[] startTSIdxs = new int[types.length];
			int[] srcipIdxs = new int[types.length];
			int[] dstipIdxs = new int[types.length];
			int[] srcportIdxs = new int[types.length];
			int[] dstportIdxs = new int[types.length];
			int[] protocolIdxs = new int[types.length];
			int[] recordIdxs = new int[types.length];
			for (int i = 0; i < types.length; i++) {
				// System.out.println("type=" + types[i] + ", where=" +
				// where[i]);
				qrs[i] = get(queryID, n, 0, types[i], where[i], "startts asc");
				startTSIdxs[i] = -1;
				srcipIdxs[i] = -1;
				dstipIdxs[i] = -1;
				srcportIdxs[i] = -1;
				dstportIdxs[i] = -1;
				protocolIdxs[i] = -1;
				recordIdxs[i] = 0;
				if (qrs[i] != null && qrs[i].flexRecordGroup != null) {
					startTSIdxs[i] = qrs[i].flexRecordGroup
							.getFieldIdx("startts");
					srcipIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("srcip");
					dstipIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("dstip");
					dstportIdxs[i] = qrs[i].flexRecordGroup
							.getFieldIdx("dstport");
					srcportIdxs[i] = qrs[i].flexRecordGroup
							.getFieldIdx("srcport");
					protocolIdxs[i] = qrs[i].flexRecordGroup
							.getFieldIdx("protocol");
				}
			}
			Vector records = new Vector();
			long startTime = -1;
			Timestamp startTS = null;
			int idx = 0;
			Timestamp lastMinTS = null;
			while (idx < n) {
				Timestamp minTS = null;
				int minIdx = -1;
				for (int x = 0; x < qrs.length; x++) {
					if (qrs[x] != null && qrs[x].flexRecordGroup != null) {
						FlexRecord rec = qrs[x].flexRecordGroup
								.getRecord(recordIdxs[x]);
						if (rec != null && startTSIdxs[x] != -1) {
							rec.startTSIdx = startTSIdxs[x];
							rec.dstipIdx = dstipIdxs[x];
							rec.srcipIdx = srcipIdxs[x];
							rec.srcportIdx = srcportIdxs[x];
							rec.dstportIdx = dstportIdxs[x];
							rec.protoIdx = protocolIdxs[x];
							String tsStr = rec.values[startTSIdxs[x]];
							Timestamp ts = Timestamp.valueOf(tsStr);
							if (minTS == null || ts.before(minTS)) {
								minTS = ts;
								minIdx = x;
							}
						}
					}
				}
				if (minTS == null) {
					idx = n;
				} else {

					FlexRecord rec = qrs[minIdx].flexRecordGroup
							.getRecord(recordIdxs[minIdx]);
					rec.colNames = qrs[minIdx].flexRecordGroup.getColumnNames();
					if (rec.values.length > qr.maxCol)
						qr.maxCol = rec.values.length;
					recordIdxs[minIdx]++;
					records.addElement(rec);
					// System.out.println(rec.toString());
				}
				idx++;
			}
			qr.flexRecordGroup = new FlexRecordGroup();
			FlexRecord[] recs = new FlexRecord[records.size()];
			for (int i = 0; i < recs.length; i++) {
				recs[i] = (FlexRecord) records.elementAt(i);
			}
			qr.flexRecordGroup.records = recs;
			return qr;
		} catch (Exception E) {
			E.printStackTrace();
		}
		return null;
	}

}
